/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package eproject_sem2;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author h
 */
public class ConnectSQL {
    public static List read() {
        List sd = new ArrayList();
        ObjectInputStream ois = null;
        try {
            ois = new ObjectInputStream(new FileInputStream(new File("student.txt")));
            sd = (List) ois.readObject();
            ois.close();
        } catch (Exception ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ois.close();
            } catch (IOException ex) {
                Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return sd;
    }

    /**
     *
     * @param sd
     * @throws IOException
     */
    public static void write(List sd) throws IOException {
        ObjectOutputStream oos = null;
        File f = new File("student.txt");
        try {
            f.delete();
            oos = new ObjectOutputStream(new FileOutputStream(f));
            oos.writeObject(sd);
            System.out.println("Luu thanh cong!");
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            oos.close();
        }

    }
    
    final static String user = (String) read().get(2);
    final static String pass = (String) read().get(3);
    final static String ulr = "jdbc:sqlserver://"+(String) read().get(0)+":"+(String) read().get(1)+";" + "databaseName=Goldtime_Data";
    final static String DriverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    Connection con = null;

    public ResultSet getData(String str) {
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            Statement Stm = con.createStatement();

            return (Stm.executeQuery(str));
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void updateData(String str) {
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            PreparedStatement Psttm = con.prepareStatement(str);
            Psttm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public void ShowDP(String t1, String t2, String table, JTable sa) {
        try {
            //khai bao model de hien thi bang
            DefaultTableModel listtableModel = new DefaultTableModel();
            Vector tableR1 = new Vector();
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                Vector r = new Vector();
                r.add(rs.getString(2));
                r.add(rs.getString(3));
                tableR1.add(r);
            }
            //goi mo do hien thi thong tin ra bang
            Vector<String> columnNames = new Vector<String>();
            columnNames.addElement("" + t1 + "");
            columnNames.addElement("" + t2 + "");
            sa.setModel(listtableModel);
            listtableModel.setDataVector(tableR1, columnNames);
        } catch (SQLException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public float GetPrice(String CT) throws ClassNotFoundException, SQLException {
        float kq = 0;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from VotesSent";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                String k1 = rs.getString("Waybill");
                if (k1.equalsIgnoreCase(CT)) {
                    kq = rs.getFloat("Totalamount");
                    break;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }

    public String Get(String u, String table, String c, String c1) {
        String rs1 = null;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                String k1 = rs.getString(c);
                if (k1.equalsIgnoreCase(u)) {
                    rs1 = rs.getString(c1);
                    break;
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs1;
    }

    public void ShowTable(JTable sa, String where, String str1, String str2, String str3, String str4, String str5, String str6, String d1, String d2, String d3) {
        try {
            //khai bao model de hien thi bang
            DefaultTableModel listtableModel = new DefaultTableModel();
            Vector tableR1 = new Vector();
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql = "SELECT *FROM List_VotesSent " + where + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                Vector r = new Vector();
                int i = 0;
                String[] temp = new String[3];
                String k1 = rs.getString(3);
                temp = k1.split("/");
                String k2 = temp[i];
                String k3 = temp[i + 1];
                String k4 = temp[i + 2];
                if ("All".equalsIgnoreCase(d1)) {
                    if ("All".equalsIgnoreCase(d2)) {
                        if ("All".equalsIgnoreCase(d3)) {
                            r.add(rs.getString(str1));
                            r.add(rs.getString(str2));
                            r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                            r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                            r.add(GetPrice(rs.getString(str1)));
                            r.add(rs.getString(str3));
                            r.add(rs.getString(str4));
                            r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                            r.add(rs.getString(str5));
                            r.add(rs.getString(str6));
                            tableR1.add(r);
                        } else {
                            if (k4 == null ? d3 == null : k4.equalsIgnoreCase(d3)) {
                                r.add(rs.getString(str1));
                                r.add(rs.getString(str2));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                                r.add(GetPrice(rs.getString(str1)));
                                r.add(rs.getString(str3));
                                r.add(rs.getString(str4));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                                r.add(rs.getString(str5));
                                r.add(rs.getString(str6));
                                tableR1.add(r);
                            }

                        }
                    } else {
                        if ("All".equalsIgnoreCase(d3)) {
                            if (k3 == null ? d2 == null : k3.equalsIgnoreCase(d2)) {
                                r.add(rs.getString(str1));
                                r.add(rs.getString(str2));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                                r.add(GetPrice(rs.getString(str1)));
                                r.add(rs.getString(str3));
                                r.add(rs.getString(str4));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                                r.add(rs.getString(str5));
                                r.add(rs.getString(str6));
                                tableR1.add(r);
                            }
                        } else {
                            if (k3 == null ? d2 == null : k3.equalsIgnoreCase(d2)) {
                                if (k4 == null ? d3 == null : k4.equalsIgnoreCase(d3)) {
                                    r.add(rs.getString(str1));
                                    r.add(rs.getString(str2));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                                    r.add(GetPrice(rs.getString(str1)));
                                    r.add(rs.getString(str3));
                                    r.add(rs.getString(str4));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                                    r.add(rs.getString(str5));
                                    r.add(rs.getString(str6));
                                    tableR1.add(r);
                                }
                            }
                        }
                    }
                } else {
                    if ("All".equalsIgnoreCase(d2)) {
                        if ("All".equalsIgnoreCase(d3)) {
                            if (k2 == null ? d1 == null : k2.equalsIgnoreCase(d1)) {
                                r.add(rs.getString(str1));
                                r.add(rs.getString(str2));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                                r.add(GetPrice(rs.getString(str1)));
                                r.add(rs.getString(str3));
                                r.add(rs.getString(str4));
                                r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                                r.add(rs.getString(str5));
                                r.add(rs.getString(str6));
                                tableR1.add(r);
                            }
                        } else {
                            if (k2 == null ? d1 == null : k2.equalsIgnoreCase(d1)) {
                                if (k4 == null ? d3 == null : k4.equalsIgnoreCase(d3)) {
                                    r.add(rs.getString(str1));
                                    r.add(rs.getString(str2));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                                    r.add(GetPrice(rs.getString(str1)));
                                    r.add(rs.getString(str3));
                                    r.add(rs.getString(str4));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                                    r.add(rs.getString(str5));
                                    r.add(rs.getString(str6));
                                    tableR1.add(r);
                                }
                            }

                        }
                    } else {
                        if ("All".equalsIgnoreCase(d3)) {
                            if (k4 == null ? d3 == null : k4.equalsIgnoreCase(d3)) {
                                if (k2 == null ? d1 == null : k2.equalsIgnoreCase(d1)) {
                                    r.add(rs.getString(str1));
                                    r.add(rs.getString(str2));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                                    r.add(GetPrice(rs.getString(str1)));
                                    r.add(rs.getString(str3));
                                    r.add(rs.getString(str4));
                                    r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                                    r.add(rs.getString(str5));
                                    r.add(rs.getString(str6));
                                    tableR1.add(r);
                                }
                            }
                        } else {
                            if (k2 == null ? d1 == null : k2.equalsIgnoreCase(d1)) {
                                if (k3 == null ? d2 == null : k3.equalsIgnoreCase(d2)) {
                                    if (k4 == null ? d3 == null : k4.equalsIgnoreCase(d3)) {
                                        r.add(rs.getString(str1));
                                        r.add(rs.getString(str2));
                                        r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "CustomersID"));
                                        r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "DeliveryAddress"));
                                        r.add(GetPrice(rs.getString(str1)));
                                        r.add(rs.getString(str3));
                                        r.add(rs.getString(str4));
                                        r.add(Get(rs.getString(str1), "VotesSent", "Waybill", "EmployeeID"));
                                        r.add(rs.getString(str5));
                                        r.add(rs.getString(str6));
                                        tableR1.add(r);
                                    }
                                }
                            }

                        }
                    }
                    k2 = null;
                    k3 = null;
                    k4 = null;
                    i = i + 3;
                    temp = null;

                }
            }
            //goi mo do hien thi thong tin ra bang
            Vector<String> columnNames = new Vector<String>();
            columnNames.addElement("" + str1 + "");
            columnNames.addElement("" + str2 + "");
            columnNames.addElement("CustomersID");
            columnNames.addElement("DeliveryAddress");
            columnNames.addElement("Totalamount");
            columnNames.addElement("" + str3 + "");
            columnNames.addElement("" + str4 + "");
            columnNames.addElement("EmployeeID Business");
            columnNames.addElement("EmployeeID Accounting");
            columnNames.addElement("EmployeeID Accounting");
            sa.setModel(listtableModel);
            listtableModel.setDataVector(tableR1, columnNames);
        } catch (SQLException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectSQL.class.getName()).log(Level.SEVERE, null, ex);
        }


    }
}
